﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Collections.Specialized;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using Limilabs.Mail;
using Limilabs.Client.SMTP;
using Limilabs.Mail.Fluent;
using ControlYSeguimiento.Logging;
using System.ComponentModel;
using System.IO;

namespace ControlYSeguimiento.Domain
{
    [Serializable]
    [DataObject]
    public sealed class Facade
    {
        private static Facade instance;
        private static Object SyncRoot = new Object();
        private static string Culture = "ES";
        private static string ConnectionString;
        private static string SmtpUser;
        private static string SmtpPassword;
        private static string SmtpAddress;
        private static string SmtpPort;
        private static string SmtpSSLEnabled;
        private static string SenderAddress;
        private static object CreateTransactionLock;

        #region Constructor
        private Facade()
        {
            this.Init();
            CreateTransactionLock = new object();
        }

        private void Init()
        {
            string strField;
            NameValueCollection appSettings = ConfigurationManager.AppSettings;

            foreach (String strEntry in appSettings.AllKeys)
            {
                switch (strEntry)
                {
                    case "ConnectionString":
                        Facade.ConnectionString = appSettings[strEntry];
                        break;
                    case "SmtpAddress":
                        Facade.SmtpAddress = appSettings[strEntry];
                        break;
                    case "SmtpPort":
                        Facade.SmtpPort = appSettings[strEntry];
                        break;
                    case "SmtpUser":
                        Facade.SmtpUser = appSettings[strEntry];
                        break;
                    case "SmtpPassword":
                        Facade.SmtpPassword = appSettings[strEntry];
                        break;
                    case "SmtpSSLEnabled":
                        Facade.SmtpSSLEnabled = appSettings[strEntry];
                        break;
                    case "SenderAddress":
                        Facade.SenderAddress = appSettings[strEntry];
                        break;
                }
            }


        }
        #endregion Constructor

        #region Singleton
        public static Facade Instance
        {
            get
            {
                if (instance == null)
                {
                    lock (SyncRoot)
                    {
                        if (instance == null)
                        {
                            instance = new Facade();
                        }
                    }
                }

                return instance;
            }
        }
        #endregion Singleton

        #region Smtp Setup
        public string GetSmtpAddress()
        {
            return Facade.SmtpAddress;
        }
        public string GetSmtpPort()
        {
            return Facade.SmtpPort;
        }
        public string GetSmtpUser()
        {
            return Facade.SmtpUser;
        }
        public string GetSmtpPassword()
        {
            return Facade.SmtpPassword;
        }
        public string GetSmtpSSLEnabled()
        {
            return Facade.SmtpSSLEnabled;
        }
        public string GetSenderAddress()
        {
            return Facade.SenderAddress;
        }
        #endregion Smtp Setup

        #region Mail Body
        public string GetHtmlBodyMail(string strUser, string strPass, string mobilePlatform = "And")
        {
            string downloadLink = String.Empty;
            switch (mobilePlatform)
            {
                case "And":
                    downloadLink = @"http://www.controlyseguimiento.com.ar/Downloads/PositionCYSand.rar";
                    break;
                case "Sim":
                    downloadLink = @"http://www.controlyseguimiento.com.ar/Downloads/Sim.jar";
                    break;
                case "BB":
                    downloadLink = @"http://www.controlyseguimiento.com.ar/Downloads/BB.rar";
                    break;
            }


            return "<table width='100%' cellpadding='10' cellspacing='0' bgcolor='#eaeaea'>" +
                    "<tbody>" +
                       "<tr>" +
                           "<td valign='top' align='center'>" +
                               "<table width='600' cellpadding='0' cellspacing='0' style='border:solid 1px #cccccc'>" +
                                   "<tbody>" +
                                       "<tr>" +
                                           "<td style='background-color:#ff8617;border-top:0px solid #000000;border-bottom:1px solid #ffffff;text-align:center' align='center'>" +
                                               "<span style='font-size:14px;color:#fff;line-height:200%;font-family:verdana;text-decoration:none'>" +
                                                   "<b>Celosos y Celosas </b> -Ten&eacute; el control de tu relaci&oacute;n. " +
                                                   "<a href='http://celosasycelosas.webs.com' target='_blank'>Learn more</a>" +
                                               "</span>" +
                                           "</td>" +
                                       "</tr>" +
                                       "<tr>" +
                                           "<td align='left' valign='middle' style='padding:20px;background-image:url(http://zbctech.com.ar/images/Fondos/800x600/fondo.png)'>" +
                                               "<table width='100%'>" +
                                                   "<tr>" +
                                                       "<td width='15%'>" +
                                                           "<img alt='ipa2' src='http://profile.ak.fbcdn.net/hprofile-ak-snc4/276214_100004075153523_509965830_n.jpg' style='width:100px;height:100px;'/>" +
                                                       "</td>" +
                                                       "<td width='10%'>" +
                                                           "<h1 style='font-family:Trebuchet MS;font-weight:normal;font-size:36px;margin:0'>" +
                                                           "<span style='color:#006d9f'></span>" +
                                                           "</h1>" +
                                                       "</td>" +
                                                       "<td width='65%'>" +
                                                           "<h1 style='font-family:Trebuchet MS;font-weight:normal;font-size:18px;margin:0'>" +
                                                           "<span style='color:#006d9f'>" +
                                                               "<b>No te dejes enga&ntilde;ar, toma el control.</b>" +
                                                           "</span>" +
                                                           "</h1>" +
                                                       "</td>" +
                                                   "</tr>" +
                                               "</table>" +
                                           "</td>" +
                                       "</tr>" +
                                   "</tbody>" +
                               "</table>" +
                               "<table width='600' cellpadding='20' cellspacing='0' bgcolor='#FFFFFF' style='border:solid 1px #cccccc'>" +
                                   "<tbody>" +
                                       "<tr>" +
                                           "<td bgcolor='#FFFFFF' valign='top' width='400px' style='font-size:14px;color:#555555;line-height:150%;font-family:Arial,Helvetica,sans-serif;text-align:left'>" +


                                               "<p style='margin-left:10px'>" +
                                               "</p>" +
                                               "<p>" +
                                                   "Gracias por elegir Controles y Seguimiento web. Podr&aacute;s monitorear a tu pareja, tus hijos, tus mascotas, quien ó que desees. " +
                                               "</p>" +
                                               "<br/>" +
                                                "<p style='font-size: 16px;'>" +
                                                       "Si desea recibir la aplicación por mail, envíe un mail a <a href='mailto:controlesyseguimiento@gmail.com?subject=APPMAIL'>esta direcci&oacute;n </a>" +
                                                       "<br />" +
                                                       "Tambi&eacute; podes conseguir la aplicaci&oacute;n en: GOOGLE PLAY - IPHONE APPSTORE - BLACKBERRY Y NOKIA STORE                                 " +
                                               "</p>                               " +
                                               "<p><b>ATENCION: Quizas tu celular precise habilitar la opci&oacute;n 'Aceptar aplicaciones de origen desconocido' en el men&uacute; de ajustes</b></p>" +
                                               "<p style='font-size: 16px;'>" +
                                                   "-DEMO GRATIS <br/>" +
                                                   "-Para aplicaci&oacute;n FULL (U$D 1.99) puedes elegir alguna de las siguientes opciones de pago:      " +
                                                   "<br/>" +

                                                   "<table width='100%'>" +
                                                       "<tr>" +
                                                           "<td width='100%'>" +
                                                               "<a href='https://www.mercadopago.com/mla/checkout/pay?pref_id=06581670-f42f-47a3-965f-6c9d76070f7c' target='_blank'>" +
                                                                   "<img alt='MercadoPago' width='170px' height='50px' src='http://mercadopago.com/org-img/MP3/home/logomp3.gif' /> MERCADOPAGO ARGENTINA" +
                                                               "</a>" +
                                                           "</td>" +
                                                       "</tr>" +
                                                   "</table> " +
                                                   "<br/>" +
                                                   "<br/> " +

                                                   "<table width='100%'>" +
                                                       "<tr>" +
                                                           "<td width='100%'>" +
                                                               "<a href='https://www.mercadopago.com/mlb/checkout/pay?pref_id=122891310-8e6bbf6c-4166-415c-8383-32b2ded6cdc8' target='_blank'>" +
                                                                   "<img alt='MercadoPago' width='170px' height='50px' src='http://mercadopago.com/org-img/MP3/home/logomp3.gif' /> MERCADOPAGO BRASIL" +
                                                               "</a>" +
                                                           "</td>" +
                                                       "</tr>" +
                                                   "</table> " +
                                                   "<br/>" +
                                                   "<br/> " +

                                                   "<table width='100%'>" +
                                                       "<tr>" +
                                                           "<td width='100%'>" +
                                                              "<img alt='Paypal' width='170px' height='50px' src='https://www.paypal.com/en_US/i/logo/paypal_logo.gif' />" +
                                                              "<form target='_blank' action='https://www.paypal.com/cgi-bin/webscr' method='post'>" +
                                                                   "<input type='hidden' name='cmd' value='_s-xclick'>" +
                                                                   "<input type='hidden' name='hosted_button_id' value='XKF3DS78W368L'>" +
                                                                   "<input type='image' src='https://www.paypalobjects.com/en_US/i/btn/btn_buynowCC_LG.gif' border='0' name='submit' alt='PayPal - The safer, easier way to pay online!'>" +
                                                                   "<img alt='' border='0' src='https://www.paypalobjects.com/en_US/i/scr/pixel.gif' width='1' height='1'>" +
                                                               "</form>" +
                                                           "</td>" +
                                                       "</tr>" +
                                                   "</table>" +
                                                   "<p style='font-size: 10px;'> Una vez registrado el pago, se activará el servicio FULL.</p>" +
                                                   "<br/> " +
                                                   "<br/> " +
                                               "</p>" +
                                           "</td>" +
                                       "</tr>" +
                                       "<tr>" +
                                           "<td align='center' width='100%' valign='middle' height='300px'>" +
                                                "<img alt='gps' width='80%' height='80%' src='http://d2bm3ljpacyxu8.cloudfront.net/width/774/crop/0,0,774x427/celosasycelosas.webs.com/photos/undefined/control.png' />" +
                                           "</td>" +
                                       "</tr>" +
                                       "<tr>" +
                                           "<td width='100%'>" +
                                               "<p style='font-size: 16px;'> " +
                                                   "Si ya realizo el pago siga las instrucciones <br /> INSTRUCCIONES:" +
                                                   "<br/>         " +
                                                   "1-Descargar la aplicaci&oacute;n, instalar en el tel&eacute;fono celular y encenderla. (Descarga: <a href='" + downloadLink + "' target='_blank'>" + downloadLink + "</a> ) <br/> " +
                                                   "2-Ingresar al siguiente link de seguimiento online:" +
                                                   "<a href='http://www.controlyseguimiento.com.ar/' target='_blank'>SEGUMIENTO ONLINE http://www.controlyseguimiento.com.ar</a> " +
                                                    "<br/> " +
                                                   "3-Utilizar su nombre de usuario y contraseña para loguearse. <br/> " +
                                                   "Nombre de usuario: <b>" + strUser + "</b> <br/> " +
                                                   "Contrase&ntilde;a: <b>" + strPass + "</b> <br/> " +
                                                    "<br />" +
                                                    "Este nuevo aplicativo te informará tu ID y un codigo de activación. Guarda estos dos datos. Al ingresar al nuevo site de seguimiento online: <br/>" + "<a href=\"http://www.controlyseguimiento.com.ar\"></a>" +

"a) Crea un usuario ingresando tu mail y una clave. Luego logueate. <br/>" +
"b) Para activar el dispositivo tenes que ir a donde dice 'target' y dar click. Te aparecera un cuadro donde podras modificar el ID y el CODE. Ahi coloca edit y pon los datos que te entrego el aplicativo. Luego preciona 'Update' y por ultimo 'ready'. <br/>" +
"c) Para ubicar la locación presionar 'watch in map'. <br/>" +


                                                    "**********************************************************************************" +
                                                    "<br />-Email &eacute; generado automaticamente com seus dados e senha. So voce conoce sua senha." +
                                                    "<br />-Email is generated automatically with your user and password. Only you know your password" +
                                                    "<br />-Email generado automaticamente con su usuario y contrase&ntilde;a. Solo usted conoce su contrase&ntilde;a." +


                                               "</p>" +
                                                  "<br/>" +
                                           "</td>" +
                                       "</tr>" +
                                       "<tr>" +
                                           "<td align='center' width='100%'>" +
                                               "<br/>   " +
                                               "<p style='font-size:12px;'>" +
                                                   "Momentaneamente para celulares con sistemas operativos android(samsung,motorola,etc) y symbian(nokia). Blackberry y iPhone muy pronto...  " +
                                               "</p>" +
                                               "<p>" +
                                               "Cualquier consulta escribanos a <a href='mailto:controlesyseguimiento@gmail.com?subject=SO'>controlesyseguimiento@gmail.com</a>" +
                                               "</p>" +
                                           "</td>" +
                                       "</tr>  " +
                                       "<tr>" +
                                           "<td style='background-color:#747474;border-top:10px solid #ffffff;text-align:left' valign='top' colspan='2'>" +
                                               "<span style='font-size:10px;color:#fff;line-height:100%;font-family:verdana'>" +
                                                   "<a style='color:#fff' href='http://celosasycelosas.webs.com/contact' target='_blank'>Pol&iacute;tica de privacidad</a>" +
                                                    "| " +
                                                   "<a style='color:#fff' href='http://celosasycelosas.webs.com/contact' target='_blank'>Contacto</a>" +
                                                   "<br/>" +
                                                   "<br/>" +
                                                   "Celosos y celosas web" +
                                                   "<br/>" +
                                                   "<br/>" +
                                                       "<center>" +
                                                               "<a style='align:center; color:#fff;' href='http://celosasycelosas.webs.com/' target='_blank'>www.celosasycelosas.webs.com</a>" +
                                                       "</center>							" +
                                                   "<br/>" +
                                               "</span>" +
                                           "</td>" +
                                       "</tr>" +
                                   "</tbody>" +
                               "</table>" +
                           "</td>" +
                       "</tr>" +
                   "</tbody>" +
               "</table>";
        }
        #endregion Mail Body

        #region Users
        public User GetUserById(int idParam)
        {
            User user = null;
            #region Using SQL Connection
            using (SqlConnection objSQLConnection = new SqlConnection())
            {
                objSQLConnection.ConnectionString = Facade.ConnectionString;
                objSQLConnection.Open();

                #region Using Sql Command
                using (SqlCommand objSQLCommand = new SqlCommand())
                {


                    objSQLCommand.CommandType = CommandType.Text;
                    objSQLCommand.CommandText = "SELECT [userId] " +
                                                ",[adminName] " +
                                                ",[password] " +
                                                ",[mail] " +
                                                "FROM [dbo].[Users] with(nolock) WHERE UserId=" + idParam.ToString();

                    Logger.Instance.Debug(this, objSQLCommand.CommandText);

                    objSQLCommand.Connection = objSQLConnection;

                    #region Using SqlDataReader
                    using (SqlDataReader objSQLReader = objSQLCommand.ExecuteReader())
                    {

                        if (objSQLReader.HasRows == true)
                        {
                            if (objSQLReader.Read())
                            {
                                user = new User();
                                user.Id = Convert.ToInt32(objSQLReader[0].ToString());
                                user.Name = objSQLReader[1].ToString();
                                user.Password = objSQLReader[2].ToString();
                                user.Mail = objSQLReader[3].ToString();
                            }

                        }

                    }
                    #endregion
                }
                #endregion
                objSQLConnection.Close();
            }
            #endregion
            return user;
        }
        public User GetUserByUsernameAndPassword(string usernameParam, string passwordParam)
        {
#if DEBUG == true
            return new User { Mail = "hernan.zbucki@gmail.com", Name = "Hernan Zbucki", Password = "pepe", Id = 1 };
#endif
            User user = null;

            #region Using SQL Connection
            using (SqlConnection objSQLConnection = new SqlConnection())
            {
                objSQLConnection.ConnectionString = Facade.ConnectionString;
                objSQLConnection.Open();

                #region Using Sql Command
                using (SqlCommand objSQLCommand = new SqlCommand())
                {


                    objSQLCommand.CommandType = CommandType.Text;
                    objSQLCommand.CommandText = "SELECT [userId] " +
                                                ",[adminName] " +
                                                ",[password] " +
                                                ",[mail] " +
                                                "FROM [dbo].[Users] with(nolock) WHERE adminName='" + usernameParam + "' and password='" + passwordParam + "'";

                    Logger.Instance.Debug(this, objSQLCommand.CommandText);

                    objSQLCommand.Connection = objSQLConnection;

                    #region Using SqlDataReader
                    using (SqlDataReader objSQLReader = objSQLCommand.ExecuteReader())
                    {

                        if (objSQLReader.HasRows == true)
                        {
                            if (objSQLReader.Read())
                            {
                                user = new User();
                                user.Id = Convert.ToInt32(objSQLReader[0].ToString());
                                user.Name = objSQLReader[1].ToString();
                                user.Password = objSQLReader[2].ToString();
                                user.Mail = objSQLReader[3].ToString();
                            }

                        }

                    }
                    #endregion
                }
                #endregion
                objSQLConnection.Close();
            }
            #endregion

            return user;
        }
        #endregion Users

        #region Customer
        public Customer GenerateMemoryCustomer(string mail, string name, string username, string surname, string password)
        {
            Customer newCustomer = new Customer();
            newCustomer.EnableAtFirstUse = true;
            newCustomer.Enable = false;
            newCustomer.Mail = mail;
            newCustomer.MapCoordinate = new Coordinate("0", "0");
            newCustomer.MapZoom = 5;
            newCustomer.Name = name;
            newCustomer.RefreshMapTimer = "30000";
            newCustomer.Surname = surname;
            newCustomer.UserName = username;
            newCustomer.Password = password;

            return newCustomer;
        }
        public Customer ValidateCustomerByNameAndPassword(string userNameParam, string passwordParam)
        {
            Customer customer = null;
            #region Using SQL Connection
            using (SqlConnection objSQLConnection = new SqlConnection())
            {
                objSQLConnection.ConnectionString = Facade.ConnectionString;
                objSQLConnection.Open();

                #region Using Sql Command
                using (SqlCommand objSQLCommand = new SqlCommand())
                {


                    objSQLCommand.CommandType = CommandType.Text;
                    objSQLCommand.CommandText = "SELECT TOP 1 [customerId] " +
                                              ",[name]  " +
                                              ",[surname]   " +
                                              ",[password]   " +
                                              ",[mail]  " +
                                              ",[enable]    " +
                                              ",[enableAtFirstUse]  " +
                                              ",[mapLatitude]   " +
                                              ",[mapLongitude]  " +
                                              ",[mapZoom]   " +
                                              ",[platformId]    " +
                                              ",[userName] " +
                                              ",[refreshMapTimer] " +
                                              "FROM [Customers] with(nolock) WHERE userName='" + userNameParam + "' AND password='" + passwordParam + "'";
                    Logger.Instance.Debug(this, objSQLCommand.CommandText);

                    objSQLCommand.Connection = objSQLConnection;

                    #region Using SqlDataReader
                    using (SqlDataReader objSQLReader = objSQLCommand.ExecuteReader())
                    {
                        Transaction newTrans = new Transaction();
                        newTrans.CustomerAssociated = null;
                        newTrans.Date = DateTime.Now;
                        newTrans.TargetAssociated = null;
                        newTrans.Type = TransactionType.UserLoggedInType;
                        newTrans.UserAssociated = null;
                        newTrans.Value1 = userNameParam;
                        newTrans.Value2 = passwordParam;
                        newTrans.Value3 = newTrans.Value4 = newTrans.Value5 = newTrans.Value6 = null;

                        if (objSQLReader.HasRows == true)
                        {
                            if (objSQLReader.Read())
                            {
                                customer = new Customer();
                                customer.Id = Convert.ToInt32(objSQLReader[0].ToString());
                                customer.UserName = userNameParam;
                                customer.Name = objSQLReader[1].ToString();
                                customer.Surname = objSQLReader[2].ToString();
                                customer.Password = objSQLReader[3].ToString();
                                customer.Mail = objSQLReader[4].ToString();
                                customer.Enable = Convert.ToBoolean(objSQLReader[5].ToString());
                                customer.EnableAtFirstUse = Convert.ToBoolean(objSQLReader[6].ToString());
                                customer.MapCoordinate = new Coordinate(objSQLReader[7].ToString(), objSQLReader[8].ToString());
                                customer.MapZoom = Convert.ToInt32(objSQLReader[9].ToString());
                                customer.CustomerPlatform = Facade.Instance.GetPlatformById(Convert.ToInt32(objSQLReader[10].ToString()));
                                customer.RefreshMapTimer = objSQLReader[11].ToString();
                                newTrans.CustomerAssociated = customer;
                                newTrans.Result = "00";
                            }
                            else
                            {
                                newTrans.Result = "01";
                            }

                        }
                        else
                        {
                            newTrans.Result = "02";
                        }

                        Facade.Instance.CreateTransaction(newTrans);

                    }
                    #endregion
                }
                #endregion
                objSQLConnection.Close();
            }
            #endregion

            return customer;
        }
        public int CreateCustomer(Customer newCustomer)
        {
#if DEBUG == true
            return 1;
#else
            int id = -1;
            #region Using SQL Connection
            using (SqlConnection objSQLConnection = new SqlConnection())
            {
                objSQLConnection.ConnectionString = Facade.ConnectionString;
                objSQLConnection.Open();

            #region Using Sql Command
                using (SqlCommand objSQLCommand = new SqlCommand())
                {


                    objSQLCommand.CommandType = CommandType.Text;
                    objSQLCommand.CommandText = "INSERT INTO Customers ( " +
                                              " [name]  " +
                                              ",[surname]   " +
                                              ",[password]   " +
                                              ",[username]   " +
                                              ",[mail]  " +
                                              ",[enable]    " +
                                              ",[enableAtFirstUse]  " +
                                              ",[mapLatitude]   " +
                                              ",[mapLongitude]  " +
                                              ",[mapZoom]   " +
                                              ",[refreshMapTimer]   " +
                                              ",[platformId])    " +
                                              " VALUES (" +
                                              "'" + newCustomer.Name + "'," +
                                              "'" + newCustomer.Surname + "'," +
                                              "'" + newCustomer.Password + "'," +
                                              "'" + newCustomer.Mail + "'," +
                                              "'" + newCustomer.Mail + "'," +
                                              ((newCustomer.Enable == true) ? "1" : "0") + "," +
                                              ((newCustomer.EnableAtFirstUse == true) ? "1" : "0") + "," +
                                              "'" + newCustomer.MapCoordinate.Latitude.ToString() + "'," +
                                              "'" + newCustomer.MapCoordinate.Longitude.ToString() + "'," +
                                              "'" + newCustomer.MapZoom.ToString() + "'," +
                                              "'" + newCustomer.RefreshMapTimer + "'," +
                                              "1" +
                                              ")";
                    Logger.Instance.Debug(this, objSQLCommand.CommandText);
                    objSQLCommand.Connection = objSQLConnection;

            #region Using SqlDataReader
                    using (SqlDataReader objSQLReader = objSQLCommand.ExecuteReader())
                    {
                        id = this.GetCustomerByName(newCustomer.UserName).Id;
                    }
            #endregion
                }
            #endregion
                objSQLConnection.Close();
            }
            #endregion

            return id;
#endif
        }
        public Customer GetCustomerByName(string userNameParam)
        {
#if DEBUG == true
            return new Customer
            {
                CustomerPlatform = new Platform { Id = 1 },
                Enable = true,
                EnableAtFirstUse = false,
                Id = 1,
                Mail = "Mail@Mail.com",
                MapCoordinate = new Coordinate("0", "0"),
                MapZoom = 5,
                Name = "Name",
                Password = "pepe",
                Surname = "surname",
                RefreshMapTimer = "30000",
                UserName = "user"
            };
#endif
            Customer customer = null;
            #region Using SQL Connection
            using (SqlConnection objSQLConnection = new SqlConnection())
            {
                objSQLConnection.ConnectionString = Facade.ConnectionString;
                objSQLConnection.Open();

                #region Using Sql Command
                using (SqlCommand objSQLCommand = new SqlCommand())
                {


                    objSQLCommand.CommandType = CommandType.Text;
                    objSQLCommand.CommandText = "SELECT TOP 1 [customerId] " +
                                              ",[name]  " +
                                              ",[surname]   " +
                                              ",[password]   " +
                                              ",[mail]  " +
                                              ",[enable]    " +
                                              ",[enableAtFirstUse]  " +
                                              ",[mapLatitude]   " +
                                              ",[mapLongitude]  " +
                                              ",[mapZoom]   " +
                                              ",[platformId]    " +
                                              ",[refreshMapTimer]    " +
                                              "FROM [Customers] with(nolock) WHERE userName='" + userNameParam + "'";
                    Logger.Instance.Debug(this, objSQLCommand.CommandText);
                    objSQLCommand.Connection = objSQLConnection;

                    #region Using SqlDataReader
                    using (SqlDataReader objSQLReader = objSQLCommand.ExecuteReader())
                    {

                        if (objSQLReader.HasRows == true)
                        {
                            if (objSQLReader.Read())
                            {
                                Logger.Instance.Debug(this, "Populando datos del cliente...");
                                customer = new Customer();
                                customer.Id = Convert.ToInt32(objSQLReader[0].ToString());
                                Logger.Instance.Debug(this, "Customer Id = " + customer.Id.ToString());
                                customer.Name = objSQLReader[1].ToString();
                                Logger.Instance.Debug(this, "Customer Name = " + customer.Name.ToString());
                                customer.Surname = objSQLReader[2].ToString();
                                Logger.Instance.Debug(this, "Customer Surname = " + customer.Surname.ToString());
                                customer.Password = objSQLReader[3].ToString();
                                Logger.Instance.Debug(this, "Customer Password = " + customer.Password.ToString());
                                customer.Mail = objSQLReader[4].ToString();
                                Logger.Instance.Debug(this, "Customer Mail = " + customer.Mail.ToString());
                                customer.Enable = Convert.ToBoolean(objSQLReader[5].ToString());
                                Logger.Instance.Debug(this, "Customer Enable = " + customer.Enable.ToString());
                                customer.EnableAtFirstUse = Convert.ToBoolean(objSQLReader[6].ToString());
                                Logger.Instance.Debug(this, "Customer EnableAtFirstUse = " + customer.EnableAtFirstUse.ToString());
                                customer.MapCoordinate = new Coordinate(objSQLReader[7].ToString(), objSQLReader[8].ToString());
                                Logger.Instance.Debug(this, "Customer coord = " + customer.MapCoordinate.LatitudeDb.ToString() + "-" + customer.MapCoordinate.LongitudeDb.ToString() + " | " + customer.MapCoordinate.Latitude + " - " + customer.MapCoordinate.Longitude);
                                customer.MapZoom = Convert.ToInt32(objSQLReader[9].ToString());
                                Logger.Instance.Debug(this, "Customer MapZoom = " + customer.MapZoom.ToString());
                                customer.CustomerPlatform = Facade.Instance.GetPlatformById(Convert.ToInt32(objSQLReader[10].ToString()));
                                customer.RefreshMapTimer = objSQLReader[11].ToString();
                            }

                        }

                    }
                    #endregion
                }
                #endregion
                objSQLConnection.Close();
            }
            #endregion
            return customer;
        }
        private Customer GetCustomerById(int idParam)
        {
            Customer customer = null;
            #region Using SQL Connection
            using (SqlConnection objSQLConnection = new SqlConnection())
            {
                objSQLConnection.ConnectionString = Facade.ConnectionString;
                objSQLConnection.Open();

                #region Using Sql Command
                using (SqlCommand objSQLCommand = new SqlCommand())
                {


                    objSQLCommand.CommandType = CommandType.Text;
                    objSQLCommand.CommandText = "SELECT TOP 1 [customerId] " +
                                              ",[name]  " +
                                              ",[surname]   " +
                                              ",[password]   " +
                                              ",[mail]  " +
                                              ",[enable]    " +
                                              ",[enableAtFirstUse]  " +
                                              ",[mapLatitude]   " +
                                              ",[mapLongitude]  " +
                                              ",[mapZoom]   " +
                                              ",[platformId]    " +
                                              ",[refreshMapTimer]    " +
                                              "FROM [Customers] with(nolock) WHERE CustomerId=" + idParam.ToString();

                    Logger.Instance.Debug(this, objSQLCommand.CommandText);
                    objSQLCommand.Connection = objSQLConnection;

                    #region Using SqlDataReader
                    using (SqlDataReader objSQLReader = objSQLCommand.ExecuteReader())
                    {

                        if (objSQLReader.HasRows == true)
                        {
                            if (objSQLReader.Read())
                            {
                                customer = new Customer();
                                customer.Id = Convert.ToInt32(objSQLReader[0].ToString());
                                customer.Name = objSQLReader[1].ToString();
                                customer.Surname = objSQLReader[2].ToString();
                                customer.Password = objSQLReader[3].ToString();
                                customer.Mail = objSQLReader[4].ToString();
                                customer.Enable = Convert.ToBoolean(objSQLReader[5].ToString());
                                customer.EnableAtFirstUse = Convert.ToBoolean(objSQLReader[6].ToString());
                                customer.MapCoordinate = new Coordinate(objSQLReader[7].ToString(), objSQLReader[8].ToString());
                                customer.MapZoom = Convert.ToInt32(objSQLReader[9].ToString());
                                customer.CustomerPlatform = Facade.Instance.GetPlatformById(Convert.ToInt32(objSQLReader[10].ToString()));
                                customer.RefreshMapTimer = objSQLReader[11].ToString();
                            }

                        }

                    }
                    #endregion
                }
                #endregion
                objSQLConnection.Close();
            }
            #endregion
            return customer;
        }
        public bool SaveCustomer(Customer customer)
        {
            bool boolReturn = false;
            if (customer == null)
            {
                throw new Exception("No esta inicializado el cliente que se desea salvar");
            }

#if DEBUG == true
            return true;
#endif


            #region Using SQL Connection
            using (SqlConnection objSQLConnection = new SqlConnection())
            {
                objSQLConnection.ConnectionString = Facade.ConnectionString;
                objSQLConnection.Open();

                #region Using Sql Command
                using (SqlCommand objSQLCommand = new SqlCommand())
                {


                    objSQLCommand.CommandType = CommandType.Text;
                    objSQLCommand.CommandText = "UPDATE Customers " +
                                              "SET [name]=  '" + customer.Name + "' " +
                                              ",[surname]=  '" + customer.Surname + "' " +
                                              ",[password]=   '" + customer.Password + "' " +
                                              ",[mail]=  '" + customer.Mail + "' " +
                                              ",[enable]=    " + ((customer.Enable == true) ? "1" : "0") + " " +
                                              ",[enableAtFirstUse]=  " + ((customer.EnableAtFirstUse == true) ? "1" : "0") + " " +
                                              ",[mapLatitude]=   '" + customer.MapCoordinate.Latitude + "' " +
                                              ",[mapLongitude]=  '" + customer.MapCoordinate.Longitude + "' " +
                                              ",[mapZoom]=   '" + customer.MapZoom.ToString() + "' " +
                                              ",[refreshMapTimer]=    " + customer.RefreshMapTimer + " " +
                                              ",[platformId]=    " + customer.CustomerPlatform.Id.ToString() + " " +
                                              " WHERE CustomerId=" + customer.Id.ToString();

                    Logger.Instance.Debug(this, objSQLCommand.CommandText);
                    objSQLCommand.Connection = objSQLConnection;

                    #region Using SqlDataReader
                    using (SqlDataReader objSQLReader = objSQLCommand.ExecuteReader())
                    {
                        boolReturn = true;
                    }
                    #endregion
                }
                #endregion
                objSQLConnection.Close();
            }
            #endregion

            return boolReturn;
        }
        #endregion Customer

        #region Culture
        public void SetCulture(string culture)
        {
            Facade.Culture = culture;
        }
        public string GetCulture()
        {
            return Facade.Culture;
        }
        #endregion Culture



        private void SetGeoPositionValuesByTarget(Target target)
        {
            target.CurrentAltitude = null;
            target.CurrentCooridinate = new Coordinate(null, null);
            target.CurrentCourse = null;
            target.CurrentSpeed = null;
            target.CurrentBatteryLevel = null;

            #region Using SQL Connection
            using (SqlConnection objSQLConnection = new SqlConnection())
            {
                objSQLConnection.ConnectionString = Facade.ConnectionString;
                objSQLConnection.Open();

                #region Using Sql Command
                using (SqlCommand objSQLCommand = new SqlCommand())
                {


                    objSQLCommand.CommandType = CommandType.Text;
                    objSQLCommand.CommandText = " SELECT TOP 1 value1, value2, value3, value4, value5, value6 FROM Transactions with(nolock) " +
                                                   " WHERE ([type] = '" + TransactionType.UpdateCoordinatesType + "' OR [type] ='" + TransactionType.UpdateCoordinatesV2Type + "') " +
                                                   " and [result] = '00' " +
                                                   " and targetId = " + target.Id.ToString() +
                                                   " ORDER by date desc ";

                    Logger.Instance.Debug(this, objSQLCommand.CommandText);

                    objSQLCommand.Connection = objSQLConnection;

                    #region Using SqlDataReader
                    using (SqlDataReader objSQLReader = objSQLCommand.ExecuteReader())
                    {

                        if (objSQLReader.HasRows == true)
                        {
                            Logger.Instance.Debug(this, "Se encontraron movimientos para el blanco " + target.Id.ToString());

                            if (objSQLReader.Read())
                            {
                                if (String.IsNullOrEmpty(objSQLReader[0].ToString()) == false)
                                    target.CurrentAltitude = objSQLReader[0].ToString();
                                else
                                    target.CurrentAltitude = "0";

                                if (String.IsNullOrEmpty(objSQLReader[1].ToString()) && String.IsNullOrEmpty(objSQLReader[2].ToString()))
                                    target.CurrentCooridinate = new Coordinate("0", "0");
                                else
                                    target.CurrentCooridinate = new Coordinate(objSQLReader[1].ToString(), objSQLReader[2].ToString());

                                if (String.IsNullOrEmpty(objSQLReader[3].ToString()) == false)
                                    target.CurrentCourse = objSQLReader[3].ToString();
                                else
                                    target.CurrentCourse = "0";

                                if (String.IsNullOrEmpty(objSQLReader[4].ToString()) == false)
                                    target.CurrentSpeed = objSQLReader[4].ToString();
                                else
                                    target.CurrentSpeed = "0";

                                if (String.IsNullOrEmpty(objSQLReader[5].ToString()) == false)
                                    target.CurrentBatteryLevel = objSQLReader[5].ToString();
                                else
                                    target.CurrentBatteryLevel = "0";

                            }

                        }


                    }
                    #endregion
                }
                #endregion
                objSQLConnection.Close();
            }
            #endregion

        }
        public IEnumerable<Coordinate> GetTraceabilityByTarget(Target target)
        {
            List<Coordinate> coordinates = null;
            #region Using SQL Connection
            using (SqlConnection objSQLConnection = new SqlConnection())
            {
                objSQLConnection.ConnectionString = Facade.ConnectionString;
                objSQLConnection.Open();

                #region Using Sql Command
                using (SqlCommand objSQLCommand = new SqlCommand())
                {


                    objSQLCommand.CommandType = CommandType.Text;
                    objSQLCommand.CommandText = "SELECT value2, value3 FROM Transactions with(nolock) " +
                                                " WHERE ([type] = '" + TransactionType.UpdateCoordinatesType + "' OR [type] ='" + TransactionType.UpdateCoordinatesV2Type + "') " +
                                                " and [result] = '00' " +
                                                " and targetId = " + target.Id +
                                                " and datediff(minute, date, getdate() ) < " + target.TraceTimeSpan + " " +
                                                " ORDER by date desc ";
                    Logger.Instance.Debug(this, objSQLCommand.CommandText);
                    objSQLCommand.Connection = objSQLConnection;

                    #region Using SqlDataReader
                    using (SqlDataReader objSQLReader = objSQLCommand.ExecuteReader())
                    {

                        if (objSQLReader.HasRows == true)
                        {
                            coordinates = new List<Coordinate>();
                            while (objSQLReader.Read())
                            {

                                Coordinate coord = new Coordinate(objSQLReader[0].ToString(), objSQLReader[1].ToString());
                                Logger.Instance.Info(this, "Agregando step de trazabilidad " + coord.Latitude + " - " + coord.Longitude);
                                coordinates.Add(coord);
                            }

                        }

                    }
                    #endregion
                }
                #endregion
                objSQLConnection.Close();
            }
            #endregion
            return coordinates;
        }
        public bool ValidateImei(string imei)
        {
#if DEBUG == true
            return false;
#endif
            bool boolReturn = false;
            #region Using SQL Connection
            using (SqlConnection objSQLConnection = new SqlConnection())
            {
                objSQLConnection.ConnectionString = Facade.ConnectionString;
                objSQLConnection.Open();

                #region Using Sql Command
                using (SqlCommand objSQLCommand = new SqlCommand())
                {


                    objSQLCommand.CommandType = CommandType.Text;
                    objSQLCommand.CommandText = "SELECT deviceId FROM Devices with(nolock) Where imei = '" + imei + "'";
                    Logger.Instance.Debug(this, objSQLCommand.CommandText);
                    objSQLCommand.Connection = objSQLConnection;

                    #region Using SqlDataReader
                    using (SqlDataReader objSQLReader = objSQLCommand.ExecuteReader())
                    {

                        boolReturn = objSQLReader.HasRows;

                    }
                    #endregion
                }
                #endregion
                objSQLConnection.Close();
            }
            #endregion

            return boolReturn;
        }
        public bool ValidateImeiAndCode(string imei, string code)
        {
            bool boolReturn = false;
            #region Using SQL Connection
            using (SqlConnection objSQLConnection = new SqlConnection())
            {
                objSQLConnection.ConnectionString = Facade.ConnectionString;
                objSQLConnection.Open();

                #region Using Sql Command
                using (SqlCommand objSQLCommand = new SqlCommand())
                {


                    objSQLCommand.CommandType = CommandType.Text;
                    objSQLCommand.CommandText = "SELECT deviceId FROM Devices with(nolock) Where imei = '" + imei + "' and code = '" + code + "'";
                    Logger.Instance.Debug(this, objSQLCommand.CommandText);
                    objSQLCommand.Connection = objSQLConnection;

                    #region Using SqlDataReader
                    using (SqlDataReader objSQLReader = objSQLCommand.ExecuteReader())
                    {

                        boolReturn = objSQLReader.HasRows;

                    }
                    #endregion
                }
                #endregion
                objSQLConnection.Close();
            }
            #endregion
            return boolReturn;
        }

        #region Target
        public Target GenerateMemoryTarget(string name, Customer cust, Device device, string altitude = "0", string latitude = "0", string longitude = "0", string speed = "0", string course = "0")
        {
            Target newTarget = new Target();
            newTarget.CurrentAltitude = altitude;
            newTarget.CurrentCooridinate = new Coordinate(latitude, longitude);
            newTarget.CurrentCourse = course;
            newTarget.CurrentSpeed = speed;
            newTarget.Customer = cust;
            newTarget.Device = device;
            newTarget.Name = name;
            newTarget.Type = new TargetType("Person");
            newTarget.ShowInMap = true;
            newTarget.Picture = Directory.GetCurrentDirectory() + @"\Styles\Images\Users\DefaultUser.jpg";
            newTarget.TraceTimeSpan = "10";
            return newTarget;
        }
        [DataObjectMethod(DataObjectMethodType.Select)]
        public IEnumerable<Target> GetTargetsFromCustomersUsername(string userNameParam)
        {
            Logger.Instance.Debug(this, "Obteniendo targets desde GetTargetsFromCustomersUsername: Param es " + userNameParam);
            return this.GetTargetsFromCustomer(this.GetCustomerByName(userNameParam));
        }
        [DataObjectMethod(DataObjectMethodType.Select)]
        public IEnumerable<Target> GetTargetsFromCustomer(Customer customer)
        {
#if DEBUG == true
            return new List<Target> { new Target 
                                        { 
                                            //CurrentAltitude = "0", 
                                            //CurrentBatteryLevel = "58", 
                                            //CurrentCooridinate = new Coordinate("1", "1"),
                                            //CurrentCourse = "12",
                                            //CurrentSpeed = "25",
                                            CurrentAltitude = null,
                                            CurrentBatteryLevel = null,
                                            CurrentCooridinate = null,
                                            CurrentCourse = null,
                                            CurrentSpeed = null,
                                            Customer = customer,
                                            Device = new Device{Enable=true,Id=1,Imei="12345",Code="CYSXXXXXX"},
                                            Id=1,
                                            Picture = Directory.GetCurrentDirectory() + @"\Styles\Images\Users\DefaultUser.jpg",
                                            Name="Tgt#1",
                                            ShowInMap=true,                                            
                                            TraceTimeSpan="10",
                                          
                                            Type=new TargetType("Person")
                                        }, 
                                      new Target 
                                        { 
                                            CurrentAltitude = "0", 
                                            CurrentBatteryLevel = "51", 
                                            CurrentCooridinate = new Coordinate("3", "-3"),
                                            CurrentCourse = "55",
                                            CurrentSpeed = "2",
                                            Customer = customer,
                                            Device = new Device{Enable=true,Id=2,Imei="23456",Code="CYSXXXXXX"},
                                            Picture = Directory.GetCurrentDirectory() + @"\Styles\Images\Users\DefaultUser.jpg",
                                            Id=2,
                                            Name="Tgt#2",
                                            ShowInMap=true,
                                            TraceTimeSpan="60",
                                          
                                            Type=new TargetType("Person")
                                        } 
            };
#endif
            List<Target> targets = null;
            #region Using SQL Connection
            using (SqlConnection objSQLConnection = new SqlConnection())
            {
                objSQLConnection.ConnectionString = Facade.ConnectionString;
                objSQLConnection.Open();

                #region Using Sql Command
                using (SqlCommand objSQLCommand = new SqlCommand())
                {


                    objSQLCommand.CommandType = CommandType.Text;
                    //objSQLCommand.CommandText = "SELECT t.[targetId] " +
                    //                            " ,t.[targetName]      " +
                    //                            " ,t.[showInMap]      " +
                    //                            " ,t.[traceTimeSpan]      " +
                    //                            " ,d.[deviceId]   " +
                    //                            " ,d.[imei]   " +
                    //                            " ,d.[enable]   " +
                    //                            " FROM [Targets] t with(nolock)   " +
                    //                            " INNER JOIN Devices d with(nolock) on t.deviceId = d.deviceId   " +
                    //                            " WHERE customerId = " + customer.Id;


                    objSQLCommand.CommandText = "SELECT t.[targetId] " +
                                                " ,t.[targetName]      " +
                                                " ,t.[showInMap]      " +
                                                " ,t.[traceTimeSpan]      " +
                                                " ,d.[deviceId]   " +
                                                " ,d.[imei]   " +
                                                " ,d.[enable]   " +
                                                " ,d.[code]   " +
                                                " ,t.[Picture]      " +
                                                " FROM [Targets] t with(nolock)   " +
                                                " INNER JOIN Devices d with(nolock) on t.deviceId = d.deviceId   " +
                                                " INNER JOIN TargetsCustomers tc with(nolock) on tc.targetId = t.targetId   " +
                                                " WHERE customerId = " + customer.Id;

                    Logger.Instance.Debug(this, objSQLCommand.CommandText);
                    objSQLCommand.Connection = objSQLConnection;

                    #region Using SqlDataReader
                    using (SqlDataReader objSQLReader = objSQLCommand.ExecuteReader())
                    {

                        if (objSQLReader.HasRows == true)
                        {
                            targets = new List<Target>();
                            while (objSQLReader.Read())
                            {
                                Target target = new Target();
                                Device device = new Device();
                                target.Id = Convert.ToInt32(objSQLReader[0].ToString());
                                target.Name = objSQLReader[1].ToString();
                                target.ShowInMap = Convert.ToBoolean(objSQLReader[2].ToString());
                                target.TraceTimeSpan = objSQLReader[3].ToString();
                                device.Id = Convert.ToInt32(objSQLReader[4].ToString());
                                device.Imei = objSQLReader[5].ToString();
                                device.Enable = Convert.ToBoolean(objSQLReader[6].ToString());
                                device.Code = objSQLReader[7].ToString();
                                if (objSQLReader[8] == null) { target.Picture = Directory.GetCurrentDirectory() + @"\Styles\Images\Users\DefaultUser.jpg"; }
                                else target.Picture = objSQLReader[8].ToString();
                                target.Device = device;
                                target.Customer = customer;
                                target.Type = new TargetType("Person");
                                Facade.Instance.SetGeoPositionValuesByTarget(target);
                                targets.Add(target);
                            }

                        }

                    }
                    #endregion
                }
                #endregion
                objSQLConnection.Close();
            }
            #endregion
            return targets;
        }
        public Target GetTargetFromCustomerAndImei(string userName, string imei)
        {
            Target target = null;

            #region Using SQL Connection
            using (SqlConnection objSQLConnection = new SqlConnection())
            {
                objSQLConnection.ConnectionString = Facade.ConnectionString;
                objSQLConnection.Open();

                #region Using Sql Command
                using (SqlCommand objSQLCommand = new SqlCommand())
                {


                    objSQLCommand.CommandType = CommandType.Text;
                    //objSQLCommand.CommandText = " SELECT t.[targetId] " +
                    //                            " ,t.[targetName]    " +
                    //                            " ,t.[deviceId]       " +
                    //                            " ,t.[showInMap]       " +
                    //                            " ,t.[traceTimeSpan]       " +
                    //                            " ,d.[imei]       " +
                    //                            " ,d.[enable]       " +
                    //                            " FROM [Targets] t with(nolock)   " +
                    //                            " INNER JOIN Devices d with(nolock) ON t.deviceId = d.deviceId " +
                    //                            " INNER JOIN Customers c with(nolock) ON t.customerId = c.customerId " +
                    //                            " WHERE c.userName = '" + userName + "'" +
                    //                            " and d.imei = '" + imei + "'";
                    objSQLCommand.CommandText = " SELECT t.[targetId] " +
                                                " ,t.[targetName]    " +
                                                " ,t.[deviceId]       " +
                                                " ,t.[showInMap]       " +
                                                " ,t.[traceTimeSpan]       " +
                                                " ,d.[imei]       " +
                                                " ,d.[enable]       " +
                                                " ,d.[code]       " +
                                                " ,t.[Picture]       " +
                                                " FROM [Targets] t with(nolock)   " +
                                                " INNER JOIN Devices d with(nolock) ON t.deviceId = d.deviceId " +
                                                " INNER JOIN TargetsCustomers tc with(nolock) ON tc.targetId = t.targetId " +
                                                " INNER JOIN Customers c with(nolock) ON tc.customerId = c.customerId " +
                                                " WHERE c.userName = '" + userName + "'" +
                                                " and d.imei = '" + imei + "'";
                    Logger.Instance.Debug(this, objSQLCommand.CommandText);
                    objSQLCommand.Connection = objSQLConnection;

                    #region Using SqlDataReader
                    using (SqlDataReader objSQLReader = objSQLCommand.ExecuteReader())
                    {

                        if (objSQLReader.HasRows == true)
                        {
                            if (objSQLReader.Read())
                            {
                                target = new Target();
                                Device device = new Device();
                                target.Id = Convert.ToInt32(objSQLReader[0].ToString());
                                target.Name = objSQLReader[1].ToString();
                                device.Id = Convert.ToInt32(objSQLReader[2].ToString());
                                target.ShowInMap = Convert.ToBoolean(objSQLReader[3].ToString());
                                target.TraceTimeSpan = objSQLReader[4].ToString();
                                device.Imei = objSQLReader[5].ToString();
                                device.Enable = Convert.ToBoolean(objSQLReader[6].ToString());
                                device.Code = objSQLReader[7].ToString();
                                if (objSQLReader[8] == null) { target.Picture = Directory.GetCurrentDirectory() + @"\Styles\Images\Users\DefaultUser.jpg"; }
                                else target.Picture = objSQLReader[8].ToString();
                                target.Device = device;
                                target.Customer = Facade.Instance.GetCustomerByName(userName);
                                target.Type = new TargetType("Person");
                                Facade.Instance.SetGeoPositionValuesByTarget(target);


                            }

                        }

                    }
                    #endregion
                }
                #endregion
                objSQLConnection.Close();
            }
            #endregion

            return target;
        }
        /// <summary>
        /// Busca una instancia de Target por id en DB. Este metodo no setea la propiedad Customer, ya que el blanco podria pertener a muchos Customers
        /// </summary>
        /// <param name="idParam">Id de target</param>
        /// <returns>Instancia de Target con el Customer asociado en null</returns>
        [DataObjectMethod(DataObjectMethodType.Select)]
        public Target GetTargetById(int idParam)
        {
#if DEBUG == true
            return new Target
            {
                CurrentAltitude = "1",
                CurrentBatteryLevel = "28",
                CurrentCooridinate = new Coordinate("1", "1"),
                CurrentCourse = "0",
                CurrentSpeed = "15",
                Customer = new Customer
                                {
                                    CustomerPlatform = new Platform { Id = 1 },
                                    Enable = true,
                                    EnableAtFirstUse = false,
                                    Id = 1,
                                    Mail = "Mail@Mail.com",
                                    MapCoordinate = new Coordinate("0", "0"),
                                    MapZoom = 5,
                                    Name = "Name",
                                    Password = "pepe",
                                    Surname = "surname",
                                    RefreshMapTimer = "30000",
                                    UserName = "user"
                                },
                Device = new Device { Enable = true, Id = 1, Imei = "123456", Code = "CYSXXXXXX" },
                Id = 1,
                Name = "Tgt#1",
                ShowInMap = true,
                Picture = Directory.GetCurrentDirectory() + @"\Styles\Images\Users\DefaultUser.jpg",
                TraceTimeSpan = "30",
                Type = new TargetType("Person")

            };
#endif



            Target target = null;
            #region Using SQL Connection
            using (SqlConnection objSQLConnection = new SqlConnection())
            {
                objSQLConnection.ConnectionString = Facade.ConnectionString;
                objSQLConnection.Open();

                #region Using Sql Command
                using (SqlCommand objSQLCommand = new SqlCommand())
                {


                    objSQLCommand.CommandType = CommandType.Text;
                    //objSQLCommand.CommandText = " SELECT t.[targetId] " +
                    //                            " ,t.[targetName]    " +
                    //                            " ,t.[deviceId]       " +
                    //                            " ,t.[showInMap]       " +
                    //                            " ,t.[traceTimeSpan]       " +
                    //                            " ,d.[imei]       " +
                    //                            " ,d.[enable]       " +
                    //                            " ,c.[username]     " +
                    //                            " FROM [Targets] t with(nolock)   " +
                    //                            " INNER JOIN Devices d with(nolock) ON t.deviceId = d.deviceId " +
                    //                            " INNER JOIN Customers c with(nolock) ON t.customerId = c.customerId " +
                    //                            " WHERE t.targetId =  " + idParam.ToString();

                    objSQLCommand.CommandText = " SELECT t.[targetId] " +
                                                " ,t.[targetName]    " +
                                                " ,t.[deviceId]       " +
                                                " ,t.[showInMap]       " +
                                                " ,t.[traceTimeSpan]       " +
                                                " ,d.[imei]       " +
                                                " ,d.[enable]       " +
                                                " ,d.[code]       " +
                                                " ,t.[Picture]       " +
                                                " FROM [Targets] t with(nolock)   " +
                                                " INNER JOIN Devices d with(nolock) ON t.deviceId = d.deviceId " +
                                                " WHERE t.targetId =  " + idParam.ToString();



                    Logger.Instance.Debug(this, objSQLCommand.CommandText);

                    objSQLCommand.Connection = objSQLConnection;

                    #region Using SqlDataReader
                    using (SqlDataReader objSQLReader = objSQLCommand.ExecuteReader())
                    {

                        if (objSQLReader.HasRows == true)
                        {
                            if (objSQLReader.Read())
                            {
                                target = new Target();
                                Device device = new Device();
                                target.Id = Convert.ToInt32(objSQLReader[0].ToString());
                                target.Name = objSQLReader[1].ToString();
                                device.Id = Convert.ToInt32(objSQLReader[2].ToString());
                                target.ShowInMap = Convert.ToBoolean(objSQLReader[3].ToString());
                                target.TraceTimeSpan = objSQLReader[4].ToString();
                                device.Imei = objSQLReader[5].ToString();
                                device.Enable = Convert.ToBoolean(objSQLReader[6].ToString());
                                device.Code = objSQLReader[7].ToString();
                                if (objSQLReader[8] == null) { target.Picture = Directory.GetCurrentDirectory() + @"\Styles\Images\Users\DefaultUser.jpg"; }
                                else target.Picture = objSQLReader[8].ToString();
                                target.Device = device;
                                target.Customer = null;//Facade.Instance.GetCustomerByName(objSQLReader[7].ToString());
                                target.Type = new TargetType("Person");
                                Facade.Instance.SetGeoPositionValuesByTarget(target);
                            }

                        }

                    }
                    #endregion
                }
                #endregion
                objSQLConnection.Close();
            }
            #endregion
            return target;
        }

        /// <summary>
        /// Busca una instancia de Target por id de Device en DB. Este metodo no setea la propiedad Customer, ya que el blanco podria pertener a muchos Customers
        /// </summary>
        /// <param name="deviceId">Id de Device</param>
        /// <returns>Instancia de Target con el Customer asociado en null</returns>
        public Target GetTargetByDeviceId(int deviceId)
        {
            Target target = null;
            #region Using SQL Connection
            using (SqlConnection objSQLConnection = new SqlConnection())
            {
                objSQLConnection.ConnectionString = Facade.ConnectionString;
                objSQLConnection.Open();

                #region Using Sql Command
                using (SqlCommand objSQLCommand = new SqlCommand())
                {


                    objSQLCommand.CommandType = CommandType.Text;
                    //objSQLCommand.CommandText = " SELECT t.[targetId] " +
                    //                            " ,t.[targetName]    " +
                    //                            " ,t.[deviceId]       " +
                    //                            " ,t.[showInMap]       " +
                    //                            " ,t.[traceTimeSpan]       " +
                    //                            " ,d.[imei]       " +
                    //                            " ,d.[enable]       " +
                    //                            " ,c.[username]     " +
                    //                            " FROM [Targets] t with(nolock)   " +
                    //                            " INNER JOIN Devices d with(nolock) ON t.deviceId = d.deviceId " +
                    //                            " INNER JOIN Customers c with(nolock) ON t.customerId = c.customerId " +
                    //                            " WHERE d.DeviceId =  " + deviceId.ToString();

                    objSQLCommand.CommandText = " SELECT t.[targetId] " +
                                                " ,t.[targetName]    " +
                                                " ,t.[deviceId]       " +
                                                " ,t.[showInMap]       " +
                                                " ,t.[traceTimeSpan]       " +
                                                " ,d.[imei]       " +
                                                " ,d.[enable]       " +
                                                " ,d.[code]       " +
                                                " ,t.[Picture]       " +
                                                " FROM [Targets] t with(nolock)   " +
                                                " INNER JOIN Devices d with(nolock) ON t.deviceId = d.deviceId " +
                                                " WHERE d.DeviceId =  " + deviceId.ToString();

                    Logger.Instance.Debug(this, objSQLCommand.CommandText);

                    objSQLCommand.Connection = objSQLConnection;

                    #region Using SqlDataReader
                    using (SqlDataReader objSQLReader = objSQLCommand.ExecuteReader())
                    {

                        if (objSQLReader.HasRows == true)
                        {
                            if (objSQLReader.Read())
                            {
                                target = new Target();
                                Device device = new Device();
                                target.Id = Convert.ToInt32(objSQLReader[0].ToString());
                                target.Name = objSQLReader[1].ToString();
                                device.Id = Convert.ToInt32(objSQLReader[2].ToString());
                                target.ShowInMap = Convert.ToBoolean(objSQLReader[3].ToString());
                                target.TraceTimeSpan = objSQLReader[4].ToString();
                                device.Imei = objSQLReader[5].ToString();
                                device.Enable = Convert.ToBoolean(objSQLReader[6].ToString());
                                device.Code = objSQLReader[7].ToString();
                                if (objSQLReader[8] == null) { target.Picture = Directory.GetCurrentDirectory() + @"\Styles\Images\Users\DefaultUser.jpg"; }
                                else target.Picture = objSQLReader[8].ToString();
                                target.Device = device;
                                target.Customer = null;//Facade.Instance.GetCustomerByName(objSQLReader[7].ToString());
                                target.Type = new TargetType("Person");
                                Facade.Instance.SetGeoPositionValuesByTarget(target);
                            }

                        }

                    }
                    #endregion
                }
                #endregion
                objSQLConnection.Close();
            }
            #endregion
            return target;
        }
        /// <summary>
        /// Busca una instancia de Target por IMEI en DB. Este metodo no setea la propiedad Customer, ya que el blanco podria pertener a muchos Customers
        /// </summary>
        /// <param name="imei">IMEI del dispositivo</param>
        /// <returns>Instancia de Target con el Customer asociado en null</returns>
        public Target GetTargetByImei(string imei)
        {
            Target target = null;
            #region Using SQL Connection
            using (SqlConnection objSQLConnection = new SqlConnection())
            {
                objSQLConnection.ConnectionString = Facade.ConnectionString;
                objSQLConnection.Open();

                #region Using Sql Command
                using (SqlCommand objSQLCommand = new SqlCommand())
                {


                    objSQLCommand.CommandType = CommandType.Text;
                    //objSQLCommand.CommandText = " SELECT t.[targetId] " +
                    //                            " ,t.[targetName]    " +
                    //                            " ,t.[deviceId]       " +
                    //                            " ,t.[showInMap]       " +
                    //                            " ,t.[traceTimeSpan]       " +
                    //                            " ,d.[imei]       " +
                    //                            " ,d.[enable]       " +
                    //                            " ,c.[username]     " +
                    //                            " FROM [Targets] t with(nolock)   " +
                    //                            " INNER JOIN Devices d with(nolock) ON t.deviceId = d.deviceId " +
                    //                            " INNER JOIN Customers c with(nolock) ON t.customerId = c.customerId " +
                    //                            " WHERE d.DeviceId =  " + deviceId.ToString();

                    objSQLCommand.CommandText = " SELECT t.[targetId] " +
                                                " ,t.[targetName]    " +
                                                " ,t.[deviceId]       " +
                                                " ,t.[showInMap]       " +
                                                " ,t.[traceTimeSpan]       " +
                                                " ,d.[imei]       " +
                                                " ,d.[enable]       " +
                                                " ,d.[code]       " +
                                                " ,t.[Picture]       " +
                                                " FROM [Targets] t with(nolock)   " +
                                                " INNER JOIN Devices d with(nolock) ON t.deviceId = d.deviceId " +
                                                " WHERE d.imei =  '" + imei + "'";

                    Logger.Instance.Debug(this, objSQLCommand.CommandText);

                    objSQLCommand.Connection = objSQLConnection;

                    #region Using SqlDataReader
                    using (SqlDataReader objSQLReader = objSQLCommand.ExecuteReader())
                    {

                        if (objSQLReader.HasRows == true)
                        {
                            if (objSQLReader.Read())
                            {
                                target = new Target();
                                Device device = new Device();
                                target.Id = Convert.ToInt32(objSQLReader[0].ToString());
                                target.Name = objSQLReader[1].ToString();
                                device.Id = Convert.ToInt32(objSQLReader[2].ToString());
                                target.ShowInMap = Convert.ToBoolean(objSQLReader[3].ToString());
                                target.TraceTimeSpan = objSQLReader[4].ToString();
                                device.Imei = objSQLReader[5].ToString();
                                device.Enable = Convert.ToBoolean(objSQLReader[6].ToString());
                                device.Code = objSQLReader[7].ToString();
                                if (objSQLReader[8] == null) { target.Picture = Directory.GetCurrentDirectory() + @"\Styles\Images\Users\DefaultUser.jpg"; }
                                else target.Picture = objSQLReader[8].ToString();
                                target.Device = device;
                                target.Customer = null;//Facade.Instance.GetCustomerByName(objSQLReader[7].ToString());
                                target.Type = new TargetType("Person");
                                Facade.Instance.SetGeoPositionValuesByTarget(target);
                            }

                        }

                    }
                    #endregion
                }
                #endregion
                objSQLConnection.Close();
            }
            #endregion
            return target;
        }
        public int CreateTarget(Target targ)
        {
#if DEBUG == true
            return 1;
#else
            int id = -1;
            #region Using SQL Connection
            using (SqlConnection objSQLConnection = new SqlConnection())
            {
                objSQLConnection.ConnectionString = Facade.ConnectionString;
                objSQLConnection.Open();

            #region Using Sql Command
                using (SqlCommand objSQLCommand = new SqlCommand())
                {


                    objSQLCommand.CommandType = CommandType.Text;
                    //objSQLCommand.CommandText = "INSERT INTO Targets " +
                    //                            " ([targetName] " +
                    //                            " ,[showInMap] " +
                    //                            " ,[traceTimeSpan] " +
                    //                            " ,[customerId] " +
                    //                            " ,[deviceId]) " +
                    //                            " VALUES( " +
                    //                            " '" + targ.Name + "', " +
                    //                            " " + ((targ.ShowInMap == true) ? "1" : "0") + ", " +
                    //                            " '" + targ.TraceTimeSpan + "', " +
                    //                            " " + targ.Customer.Id.ToString() + ", " +
                    //                            " " + targ.Device.Id.ToString() + " " +
                    //                            " )";
                    objSQLCommand.CommandText = "INSERT INTO Targets " +
                                                " ([targetName] " +
                                                " ,[showInMap] " +
                                                " ,[traceTimeSpan] " +
                                                " ,[Picture] " +
                                                " ,[deviceId]  " +
                                                " ) " +
                                                " VALUES( " +
                                                " '" + targ.Name + "', " +
                                                " " + ((targ.ShowInMap == true) ? "1" : "0") + ", " +
                                                " '" + targ.TraceTimeSpan + "', " +
                                                " '" + targ.Picture + "', " +
                                                " " + targ.Device.Id.ToString() + " " +                                              
                                                " )";

                    Logger.Instance.Debug(this, objSQLCommand.CommandText);

                    objSQLCommand.Connection = objSQLConnection;

            #region Using SqlDataReader
                    using (SqlDataReader objSQLReader = objSQLCommand.ExecuteReader())
                    {
                        id = this.GetTargetByDeviceId(targ.Device.Id).Id;
                        this.AssociateTargetWithCustomer(id, targ.Customer.Id);
                    }
            #endregion
                }
            #endregion
                objSQLConnection.Close();
            }
            #endregion

            return id;
#endif
        }

        private void AssociateTargetWithCustomer(int idTarg, int idCust)
        {
            #region Using SQL Connection
            using (SqlConnection objSQLConnection = new SqlConnection())
            {
                objSQLConnection.ConnectionString = Facade.ConnectionString;
                objSQLConnection.Open();

                #region Using Sql Command
                using (SqlCommand objSQLCommand = new SqlCommand())
                {


                    objSQLCommand.CommandType = CommandType.Text;
                    objSQLCommand.CommandText = "INSERT INTO TargetsCustomers " +
                                                " ([targetId] " +
                                                " ,[customerId]) " +
                                                " VALUES( " +
                                                " " + idTarg.ToString() + ", " +
                                                " " + idCust.ToString() + " " +
                                                " )";

                    Logger.Instance.Debug(this, objSQLCommand.CommandText);

                    objSQLCommand.Connection = objSQLConnection;

                    #region Using SqlDataReader
                    using (SqlDataReader objSQLReader = objSQLCommand.ExecuteReader())
                    {

                    }
                    #endregion
                }
                #endregion
                objSQLConnection.Close();
            }
            #endregion
        }
        [DataObjectMethod(DataObjectMethodType.Update)]
        public bool SaveTarget(Target targ)
        {
#if DEBUG == true
            return true;
#endif
            //Target targ = null;
            bool boolReturn = false;

            Logger.Instance.Debug(this, "Guardando TGT Id=" + targ.Id.ToString() + " Nombre=" + targ.Name);

            #region Using SQL Connection
            using (SqlConnection objSQLConnection = new SqlConnection())
            {
                objSQLConnection.ConnectionString = Facade.ConnectionString;
                objSQLConnection.Open();

                #region Using Sql Command
                using (SqlCommand objSQLCommand = new SqlCommand())
                {


                    objSQLCommand.CommandType = CommandType.Text;
                    objSQLCommand.CommandText = "UPDATE Targets SET " +
                                                "  [targetName]=" + "'" + targ.Name + "'" +
                                                " ,[traceTimeSpan]=" + "'" + targ.TraceTimeSpan + "'" +
                                                " ,[Picture]=" + "'" + targ.Picture + "'" +
                                                " ,[showInMap]=" + " " + ((targ.ShowInMap == true) ? "1" : "0") + " " +
                                                " WHERE targetId= " + targ.Id;


                    Logger.Instance.Debug(this, objSQLCommand.CommandText);

                    objSQLCommand.Connection = objSQLConnection;

                    #region Using SqlDataReader
                    using (SqlDataReader objSQLReader = objSQLCommand.ExecuteReader())
                    {
                        boolReturn = true;
                    }
                    #endregion
                }
                #endregion
                objSQLConnection.Close();
            }
            #endregion
            return boolReturn;
        }
        #endregion Target

        private Platform GetPlatformById(int id)
        {
            return new Platform() { Id = id };
        }


        public void FirstTimeActivationUsername(string userName)
        {
            #region Using SQL Connection
            using (SqlConnection objSQLConnection = new SqlConnection())
            {
                objSQLConnection.ConnectionString = Facade.ConnectionString;
                objSQLConnection.Open();

                #region Using Sql Command
                using (SqlCommand objSQLCommand = new SqlCommand())
                {


                    objSQLCommand.CommandType = CommandType.Text;
                    objSQLCommand.CommandText = " UPDATE [Customers] " +
                                                " SET enableAtFirstUse = 0, [enable] = 1 " +
                                                " WHERE enableAtFirstUse = 1 and username = '" + userName + "'";

                    objSQLCommand.Connection = objSQLConnection;

                    #region Using SqlDataReader
                    using (SqlDataReader objSQLReader = objSQLCommand.ExecuteReader())
                    {

                    }
                    #endregion
                }
                #endregion
                objSQLConnection.Close();
            }
            #endregion

        }
        public void FirstTimeActivationImei(string imei, string username)
        {
            #region Using SQL Connection
            using (SqlConnection objSQLConnection = new SqlConnection())
            {
                objSQLConnection.ConnectionString = Facade.ConnectionString;
                objSQLConnection.Open();

                #region Using Sql Command
                using (SqlCommand objSQLCommand = new SqlCommand())
                {


                    objSQLCommand.CommandType = CommandType.Text;
                    objSQLCommand.CommandText = " UPDATE [Devices]  " +
                                                " SET [Devices].imei = '" + imei + "' " +
                                                " FROM [Devices] " +
                                                " INNER JOIN [targets] with(nolock) on [targets].deviceId = [Devices] .deviceId " +
                                                " INNER JOIN [customers] with(nolock) on [customers].customerId = [targets].customerId " +
                                                " WHERE [customers].username = '" + username + "'";

                    objSQLCommand.Connection = objSQLConnection;

                    #region Using SqlDataReader
                    using (SqlDataReader objSQLReader = objSQLCommand.ExecuteReader())
                    {

                    }
                    #endregion
                }
                #endregion
                objSQLConnection.Close();
            }
            #endregion

        }
        public bool IsFirstTimeActivation(string userName)
        {
            bool boolReturn = false;
            #region Using SQL Connection
            using (SqlConnection objSQLConnection = new SqlConnection())
            {
                objSQLConnection.ConnectionString = Facade.ConnectionString;
                objSQLConnection.Open();

                #region Using Sql Command
                using (SqlCommand objSQLCommand = new SqlCommand())
                {


                    objSQLCommand.CommandType = CommandType.Text;
                    objSQLCommand.CommandText = " SELECT username FROM Customers with(nolock) where username ='" + userName + "' and enableAtFirstUse=1";

                    objSQLCommand.Connection = objSQLConnection;

                    #region Using SqlDataReader
                    using (SqlDataReader objSQLReader = objSQLCommand.ExecuteReader())
                    {
                        boolReturn = objSQLReader.HasRows;
                    }
                    #endregion
                }
                #endregion
                objSQLConnection.Close();
            }
            #endregion

            return boolReturn;
        }

        #region Transaction
        public int CreateTransaction(Transaction newTransaction)
        {
            int id = -1;
            lock (Facade.CreateTransactionLock)
            {

                #region Using SQL Connection
                using (SqlConnection objSQLConnection = new SqlConnection())
                {
                    objSQLConnection.ConnectionString = Facade.ConnectionString;
                    objSQLConnection.Open();

                    #region Using Sql Command
                    using (SqlCommand objSQLCommand = new SqlCommand())
                    {


                        objSQLCommand.CommandType = CommandType.Text;
                        objSQLCommand.CommandText = "INSERT INTO [Transactions] " +
                                                   "([date] " +
                                                   ",[result] " +
                                                   ",[type] " +
                                                   ",[userId] " +
                                                   ",[customerId] " +
                                                   ",[targetId] " +
                                                   ",[value1] " +
                                                   ",[value2] " +
                                                   ",[value3] " +
                                                   ",[value4] " +
                                                   ",[value5] " +
                                                   ",[value6]) " +
                                                    " VALUES( " +
                                                    "'" + newTransaction.Date.Year.ToString().PadLeft(4, '0') + newTransaction.Date.Month.ToString().PadLeft(2, '0') + newTransaction.Date.Day.ToString().PadLeft(2, '0') + " " + newTransaction.Date.Hour.ToString().PadLeft(2, '0') + ":" + newTransaction.Date.Minute.ToString().PadLeft(2, '0') + ":" + newTransaction.Date.Second.ToString().PadLeft(2, '0') + "." + newTransaction.Date.Millisecond.ToString().PadLeft(3, '0') + "'," +
                                                    "'" + newTransaction.Result + "', " +
                                                    "'" + newTransaction.Type + "', " +
                                                    ((newTransaction.UserAssociated != null) ? newTransaction.UserAssociated.Id.ToString() : "NULL") + ", " +
                                                    ((newTransaction.CustomerAssociated != null) ? newTransaction.CustomerAssociated.Id.ToString() : "NULL") + ", " +
                                                    ((newTransaction.TargetAssociated != null) ? newTransaction.TargetAssociated.Id.ToString() : "NULL") + ", " +
                                                     ((newTransaction.Value1 != null) ? "'" + newTransaction.Value1 + "'" : "NULL") + ", " +
                                                     ((newTransaction.Value2 != null) ? "'" + newTransaction.Value2 + "'" : "NULL") + ", " +
                                                     ((newTransaction.Value3 != null) ? "'" + newTransaction.Value3 + "'" : "NULL") + ", " +
                                                     ((newTransaction.Value4 != null) ? "'" + newTransaction.Value4 + "'" : "NULL") + ", " +
                                                     ((newTransaction.Value5 != null) ? "'" + newTransaction.Value5 + "'" : "NULL") + ", " +
                                                    ((newTransaction.Value6 != null) ? "'" + newTransaction.Value6 + "'" : "NULL") + " " +
                                                    ")";
                        Logger.Instance.Debug(this, objSQLCommand.CommandText);
                        objSQLCommand.Connection = objSQLConnection;

                        #region Using SqlDataReader
                        using (SqlDataReader objSQLReader = objSQLCommand.ExecuteReader())
                        {
                            id = this.GetLastTransaction().Id;
                        }
                        #endregion
                    }
                    #endregion
                    objSQLConnection.Close();
                }
                #endregion
            }

            return id;
        }
        [DataObjectMethod(DataObjectMethodType.Select)]
        public IEnumerable<Transaction> GetTransactions()
        {
#if DEBUG == true
            return new List<Transaction>{
                new Transaction{
                    Date=DateTime.Now,
                    Id=1,
                    CustomerAssociated = null,
                    Result = "00",
                    TargetAssociated = null,
                    Type = TransactionType.UpdateCoordinatesType,
                    UserAssociated = null,
                    Value1 = "1"
                }
                ,
                new Transaction{  Date=DateTime.Now,
                    Id=2,
                    CustomerAssociated = null,
                    Result = "00",
                    TargetAssociated = null,
                    Type = TransactionType.UpdateCoordinatesType,
                    UserAssociated = null,
                    Value1 = "2"}
            };
#endif
            List<Transaction> transactions = new List<Transaction>();
            Logger.Instance.Debug(this, "Obteniendo transacciones....");
            #region Using SQL Connection
            using (SqlConnection objSQLConnection = new SqlConnection())
            {
                objSQLConnection.ConnectionString = Facade.ConnectionString;
                objSQLConnection.Open();

                #region Using Sql Command
                using (SqlCommand objSQLCommand = new SqlCommand())
                {


                    objSQLCommand.CommandType = CommandType.Text;
                    objSQLCommand.CommandText = "SELECT [tranId] " +
                                                  " ,[date] " +
                                                  " ,[result] " +
                                                  " ,[type] " +
                                                  " ,[userId] " +
                                                  " ,[customerId] " +
                                                  " ,[targetId] " +
                                                  " ,[value1] " +
                                                  " ,[value2] " +
                                                  " ,[value3] " +
                                                  " ,[value4] " +
                                                  " ,[value5] " +
                                                  " ,[value6] " +
                                                  " FROM [Transactions] with(nolock) ORDER by date desc";
                    Logger.Instance.Debug(this, objSQLCommand.CommandText);
                    objSQLCommand.Connection = objSQLConnection;

                    #region Using SqlDataReader
                    using (SqlDataReader objSQLReader = objSQLCommand.ExecuteReader())
                    {
                        if (objSQLReader.HasRows == true)
                        {
                            while (objSQLReader.Read())
                            {
                                Transaction newTransaction = new Transaction();
                                newTransaction.Id = Convert.ToInt32(objSQLReader[0].ToString());
                                newTransaction.Date = Convert.ToDateTime(objSQLReader[1].ToString());
                                newTransaction.Result = objSQLReader[2].ToString();
                                newTransaction.Type = objSQLReader[3].ToString();

                                if (!String.IsNullOrEmpty(objSQLReader[4].ToString()))
                                {
                                    newTransaction.UserAssociated = Facade.Instance.GetUserById(Convert.ToInt32(objSQLReader[4].ToString()));
                                }
                                else
                                    newTransaction.UserAssociated = null;

                                if (!String.IsNullOrEmpty(objSQLReader[5].ToString()))
                                    newTransaction.CustomerAssociated = Facade.Instance.GetCustomerById(Convert.ToInt32(objSQLReader[5].ToString()));
                                else
                                    newTransaction.CustomerAssociated = null;

                                if (!String.IsNullOrEmpty(objSQLReader[6].ToString()))
                                {

                                    if ((newTransaction.TargetAssociated = Facade.Instance.GetTargetById(Convert.ToInt32(objSQLReader[6].ToString()))) == null)
                                    {
                                        throw new Exception("Disconcordancia de datos: No existe el target " + objSQLReader[6].ToString() + ", pero esta referenciado entre las Transacciones. ");
                                    }
                                    newTransaction.TargetAssociated.Customer = newTransaction.CustomerAssociated;
                                }
                                else
                                    newTransaction.TargetAssociated = null;

                                if (!String.IsNullOrEmpty(objSQLReader[7].ToString()))
                                    newTransaction.Value1 = objSQLReader[7].ToString();
                                else
                                    newTransaction.Value1 = null;

                                if (!String.IsNullOrEmpty(objSQLReader[8].ToString()))
                                    newTransaction.Value2 = objSQLReader[8].ToString();
                                else
                                    newTransaction.Value2 = null;

                                if (!String.IsNullOrEmpty(objSQLReader[9].ToString()))
                                    newTransaction.Value3 = objSQLReader[9].ToString();
                                else
                                    newTransaction.Value3 = null;


                                if (!String.IsNullOrEmpty(objSQLReader[10].ToString()))
                                    newTransaction.Value4 = objSQLReader[10].ToString();
                                else
                                    newTransaction.Value4 = null;


                                if (!String.IsNullOrEmpty(objSQLReader[11].ToString()))
                                    newTransaction.Value5 = objSQLReader[11].ToString();
                                else
                                    newTransaction.Value5 = null;


                                if (!String.IsNullOrEmpty(objSQLReader[12].ToString()))
                                    newTransaction.Value6 = objSQLReader[12].ToString();
                                else
                                    newTransaction.Value6 = null;

                                transactions.Add(newTransaction);
                            }

                        }

                    }
                    #endregion
                }
                #endregion
                objSQLConnection.Close();
            }
            #endregion
            Logger.Instance.Debug(this, "Se encontraron " + transactions.Count.ToString() + " transacciones");
            return transactions;
        }
        [DataObjectMethod(DataObjectMethodType.Select)]
        public DataTable GetTransactionsTable()
        {
            DataTable dt = new DataTable();
            DataRow dr;
            dt.Columns.Add("Id").DataType = System.Type.GetType("System.Int32");
            dt.Columns.Add("Date").DataType = System.Type.GetType("System.DateTime");
            dt.Columns.Add("Type").DataType = System.Type.GetType("System.String");
            dt.Columns.Add("Code").DataType = System.Type.GetType("System.String");
            dt.Columns.Add("Target").DataType = System.Type.GetType("System.String");
            dt.Columns.Add("Customer").DataType = System.Type.GetType("System.String");
            dt.Columns.Add("User").DataType = System.Type.GetType("System.String");
            dt.Columns.Add("V1").DataType = System.Type.GetType("System.String");
            dt.Columns.Add("V2").DataType = System.Type.GetType("System.String");
            dt.Columns.Add("V3").DataType = System.Type.GetType("System.String");
            dt.Columns.Add("V4").DataType = System.Type.GetType("System.String");
            dt.Columns.Add("V5").DataType = System.Type.GetType("System.String");
            dt.Columns.Add("V6").DataType = System.Type.GetType("System.String");

#if DEBUG == true

            dr = dt.NewRow();
            dr["Id"] = 1;
            dr["Date"] = DateTime.Now;
            dr["Type"] = TransactionType.UpdateCoordinatesType;
            dr["Code"] = "00";
            dr["Target"] = String.Empty;
            dr["Customer"] = String.Empty;
            dr["User"] = String.Empty;
            dr["V1"] = "1";
            dr["V2"] = String.Empty;
            dr["V3"] = String.Empty;
            dr["V4"] = String.Empty;
            dr["V5"] = String.Empty;
            dr["V6"] = String.Empty;
            dt.Rows.Add(dr);
            dr = dt.NewRow();
            dr["Id"] = 2;
            dr["Date"] = DateTime.Now;
            dr["Type"] = TransactionType.UpdateCoordinatesType;
            dr["Code"] = "00";
            dr["Target"] = String.Empty;
            dr["Customer"] = String.Empty;
            dr["User"] = String.Empty;
            dr["V1"] = "2";
            dr["V2"] = String.Empty;
            dr["V3"] = String.Empty;
            dr["V4"] = String.Empty;
            dr["V5"] = String.Empty;
            dr["V6"] = String.Empty;
            dt.Rows.Add(dr);

            return dt;
#endif

            Logger.Instance.Debug(this, "Obteniendo transacciones....");
            #region Using SQL Connection
            using (SqlConnection objSQLConnection = new SqlConnection())
            {
                objSQLConnection.ConnectionString = Facade.ConnectionString;
                objSQLConnection.Open();

                #region Using Sql Command
                using (SqlCommand objSQLCommand = new SqlCommand())
                {


                    objSQLCommand.CommandType = CommandType.Text;
                    objSQLCommand.CommandText = "SELECT [tranId] " +
                                                  " ,[date] " +
                                                  " ,[result] " +
                                                  " ,[type] " +
                                                  " ,[userId] " +
                                                  " ,[customerId] " +
                                                  " ,[targetId] " +
                                                  " ,[value1] " +
                                                  " ,[value2] " +
                                                  " ,[value3] " +
                                                  " ,[value4] " +
                                                  " ,[value5] " +
                                                  " ,[value6] " +
                                                  " FROM [Transactions] with(nolock) ORDER by date desc";
                    Logger.Instance.Debug(this, objSQLCommand.CommandText);
                    objSQLCommand.Connection = objSQLConnection;

                    #region Using SqlDataReader
                    using (SqlDataReader objSQLReader = objSQLCommand.ExecuteReader())
                    {
                        if (objSQLReader.HasRows == true)
                        {
                            while (objSQLReader.Read())
                            {
                                dr = dt.NewRow();

                                dr["Id"] = Convert.ToInt32(objSQLReader[0].ToString());
                                dr["Date"] = Convert.ToDateTime(objSQLReader[1].ToString());
                                dr["Type"] = objSQLReader[3].ToString();
                                dr["Code"] = objSQLReader[2].ToString();

                                if (!String.IsNullOrEmpty(objSQLReader[4].ToString()))
                                {
                                    dr["User"] = objSQLReader[4].ToString();
                                }
                                else
                                    dr["User"] = String.Empty;

                                if (!String.IsNullOrEmpty(objSQLReader[5].ToString()))
                                    dr["Customer"] = objSQLReader[5].ToString();
                                else
                                    dr["Customer"] = String.Empty;

                                if (!String.IsNullOrEmpty(objSQLReader[6].ToString()))
                                {
                                    dr["Target"] = objSQLReader[6].ToString();
                                }
                                else
                                    dr["Target"] = String.Empty;

                                if (!String.IsNullOrEmpty(objSQLReader[7].ToString()))
                                    dr["V1"] = objSQLReader[7].ToString();
                                else
                                    dr["V1"] = String.Empty;

                                if (!String.IsNullOrEmpty(objSQLReader[8].ToString()))
                                    dr["V2"] = objSQLReader[8].ToString();
                                else
                                    dr["V2"] = String.Empty;

                                if (!String.IsNullOrEmpty(objSQLReader[9].ToString()))
                                    dr["V3"] = objSQLReader[9].ToString();
                                else
                                    dr["V3"] = String.Empty;

                                if (!String.IsNullOrEmpty(objSQLReader[10].ToString()))
                                    dr["V4"] = objSQLReader[10].ToString();
                                else
                                    dr["V4"] = String.Empty;


                                if (!String.IsNullOrEmpty(objSQLReader[11].ToString()))
                                    dr["V5"] = objSQLReader[11].ToString();
                                else
                                    dr["V5"] = String.Empty;

                                if (!String.IsNullOrEmpty(objSQLReader[12].ToString()))
                                    dr["V6"] = objSQLReader[12].ToString();
                                else
                                    dr["V6"] = String.Empty;

                                dt.Rows.Add(dr);
                            }

                        }

                    }
                    #endregion
                }
                #endregion
                objSQLConnection.Close();
            }
            #endregion
            Logger.Instance.Debug(this, "Se encontraron " + dt.Rows.Count.ToString() + " transacciones");
            return dt;
        }
        private Transaction GetLastTransaction()
        {
            Transaction newTransaction = null;
            #region Using SQL Connection
            using (SqlConnection objSQLConnection = new SqlConnection())
            {
                objSQLConnection.ConnectionString = Facade.ConnectionString;
                objSQLConnection.Open();

                #region Using Sql Command
                using (SqlCommand objSQLCommand = new SqlCommand())
                {


                    objSQLCommand.CommandType = CommandType.Text;
                    objSQLCommand.CommandText = "SELECT TOP 1[tranId] " +
                                                  " ,[date] " +
                                                  " ,[result] " +
                                                  " ,[type] " +
                                                  " ,[userId] " +
                                                  " ,[customerId] " +
                                                  " ,[targetId] " +
                                                  " ,[value1] " +
                                                  " ,[value2] " +
                                                  " ,[value3] " +
                                                  " ,[value4] " +
                                                  " ,[value5] " +
                                                  " ,[value6] " +
                                                  " FROM [Transactions] with(nolock) ORDER by date desc";
                    Logger.Instance.Debug(this, objSQLCommand.CommandText);
                    objSQLCommand.Connection = objSQLConnection;

                    #region Using SqlDataReader
                    using (SqlDataReader objSQLReader = objSQLCommand.ExecuteReader())
                    {
                        if (objSQLReader.HasRows == true)
                        {
                            if (objSQLReader.Read())
                            {
                                newTransaction = new Transaction();
                                newTransaction.Id = Convert.ToInt32(objSQLReader[0].ToString());
                                newTransaction.Date = Convert.ToDateTime(objSQLReader[1].ToString());
                                newTransaction.Result = objSQLReader[2].ToString();
                                newTransaction.Type = objSQLReader[3].ToString();

                                if (!String.IsNullOrEmpty(objSQLReader[4].ToString()))
                                    newTransaction.UserAssociated = Facade.Instance.GetUserById(Convert.ToInt32(objSQLReader[4].ToString()));
                                else
                                    newTransaction.UserAssociated = null;
                                if (!String.IsNullOrEmpty(objSQLReader[5].ToString()))
                                    newTransaction.CustomerAssociated = Facade.Instance.GetCustomerById(Convert.ToInt32(objSQLReader[5].ToString()));
                                else
                                    newTransaction.CustomerAssociated = null;
                                if (!String.IsNullOrEmpty(objSQLReader[6].ToString()))
                                {
                                    newTransaction.TargetAssociated = Facade.Instance.GetTargetById(Convert.ToInt32(objSQLReader[6].ToString()));
                                    newTransaction.TargetAssociated.Customer = newTransaction.CustomerAssociated;
                                }
                                else
                                    newTransaction.TargetAssociated = null;
                                if (!String.IsNullOrEmpty(objSQLReader[7].ToString()))
                                    newTransaction.Value1 = objSQLReader[7].ToString();
                                else
                                    newTransaction.Value1 = null;
                                if (!String.IsNullOrEmpty(objSQLReader[8].ToString()))
                                    newTransaction.Value2 = objSQLReader[8].ToString();
                                else
                                    newTransaction.Value2 = null;
                                if (!String.IsNullOrEmpty(objSQLReader[9].ToString()))
                                    newTransaction.Value3 = objSQLReader[9].ToString();
                                else
                                    newTransaction.Value3 = null;
                                if (!String.IsNullOrEmpty(objSQLReader[10].ToString()))
                                    newTransaction.Value4 = objSQLReader[10].ToString();
                                else
                                    newTransaction.Value4 = null;
                                if (!String.IsNullOrEmpty(objSQLReader[11].ToString()))
                                    newTransaction.Value5 = objSQLReader[11].ToString();
                                else
                                    newTransaction.Value5 = null;
                                if (!String.IsNullOrEmpty(objSQLReader[12].ToString()))
                                    newTransaction.Value6 = objSQLReader[12].ToString();
                                else
                                    newTransaction.Value6 = null;
                            }

                        }

                    }
                    #endregion
                }
                #endregion
                objSQLConnection.Close();
            }
            #endregion

            return newTransaction;
        }
        public string GetLastUpdateDateTimeByTargetId(int p)
        {

            string date = String.Empty;
#if DEBUG == false
            #region Using SQL Connection
            using (SqlConnection objSQLConnection = new SqlConnection())
            {
                objSQLConnection.ConnectionString = Facade.ConnectionString;
                objSQLConnection.Open();

                #region Using Sql Command
                using (SqlCommand objSQLCommand = new SqlCommand())
                {


                    objSQLCommand.CommandType = CommandType.Text;
                    objSQLCommand.CommandText = "SELECT TOP 1 " +
                                                  " [date] " +
                                                  " FROM [Transactions] with(nolock) WHERE targetId=" + p.ToString() + " and type='" + TransactionType.UpdateCoordinatesV2Type + "' and result='00' ORDER by date desc";
                    Logger.Instance.Debug(this, objSQLCommand.CommandText);
                    objSQLCommand.Connection = objSQLConnection;

                    #region Using SqlDataReader
                    using (SqlDataReader objSQLReader = objSQLCommand.ExecuteReader())
                    {
                        if (objSQLReader.HasRows == true)
                        {
                            if (objSQLReader.Read())
                            {
                                date = objSQLReader[0].ToString();
                            }

                        }

                    }
                    #endregion
                }
                #endregion
                objSQLConnection.Close();
            }
            #endregion
#else
            date = System.DateTime.Now.ToString();
#endif

            return date;
        }
        #endregion Transaction
        #region Devices
        public Device GenerateMemoryDevice(string imei, string code)
        {
            Device newDevice = new Device();
            newDevice.Imei = imei;
            newDevice.Enable = true;
            newDevice.Code = code;
            return newDevice;
        }
        public int CreateDevice(Device device)
        {
#if DEBUG == true
            return 1;
#else
            int id = -1;
            #region Using SQL Connection
            using (SqlConnection objSQLConnection = new SqlConnection())
            {
                objSQLConnection.ConnectionString = Facade.ConnectionString;
                objSQLConnection.Open();

            #region Using Sql Command
                using (SqlCommand objSQLCommand = new SqlCommand())
                {


                    objSQLCommand.CommandType = CommandType.Text;
                    objSQLCommand.CommandText = "INSERT INTO [Devices] " +
                                                " ([imei] " +
                                                " ,[code] " +
                                                " ,[enable]) " +
                                                " VALUES " +
                                                " ('" + device.Imei + "', " +
                                                " '" + device.Code + "', " +
                                                " " + ((device.Enable == true) ? "1" : "0") + ")";

                    Logger.Instance.Debug(this, objSQLCommand.CommandText);
                    objSQLCommand.Connection = objSQLConnection;

            #region Using SqlDataReader
                    using (SqlDataReader objSQLReader = objSQLCommand.ExecuteReader())
                    {
                        id = this.GetDeviceByImei(device.Imei).Id;
                    }
            #endregion
                }
            #endregion
                objSQLConnection.Close();
            }
            #endregion

            return id;
#endif
        }
        public Device GetDeviceByImei(string imei)
        {
            Device dev = null;
            #region Using SQL Connection
            using (SqlConnection objSQLConnection = new SqlConnection())
            {
                objSQLConnection.ConnectionString = Facade.ConnectionString;
                objSQLConnection.Open();

                #region Using Sql Command
                using (SqlCommand objSQLCommand = new SqlCommand())
                {


                    objSQLCommand.CommandType = CommandType.Text;
                    objSQLCommand.CommandText = " SELECT [deviceId] " +
                                                  " ,[imei] " +
                                                  " ,[code] " +
                                                  " ,[enable] " +
                                                  " FROM [Devices] with(nolock) WHERE imei = '" + imei + "'";
                    Logger.Instance.Debug(this, objSQLCommand.CommandText);
                    objSQLCommand.Connection = objSQLConnection;

                    #region Using SqlDataReader
                    using (SqlDataReader objSQLReader = objSQLCommand.ExecuteReader())
                    {

                        if (objSQLReader.HasRows == true)
                        {
                            if (objSQLReader.Read())
                            {
                                dev = new Device();
                                dev.Id = Convert.ToInt32(objSQLReader[0].ToString());
                                dev.Imei = objSQLReader[1].ToString();
                                dev.Code = objSQLReader[2].ToString();
                                dev.Enable = Convert.ToBoolean(objSQLReader[3].ToString());
                            }

                        }

                    }
                    #endregion
                }
                #endregion
                objSQLConnection.Close();
            }
            #endregion
            return dev;
        }
        public Device GetDeviceById(int id)
        {
#if DEBUG == true
            return new Device { Id = 1, Code = "ABC", Enable = true, Imei = "123" };
#endif
            Device dev = null;
            #region Using SQL Connection
            using (SqlConnection objSQLConnection = new SqlConnection())
            {
                objSQLConnection.ConnectionString = Facade.ConnectionString;
                objSQLConnection.Open();

                #region Using Sql Command
                using (SqlCommand objSQLCommand = new SqlCommand())
                {


                    objSQLCommand.CommandType = CommandType.Text;
                    objSQLCommand.CommandText = " SELECT [deviceId] " +
                                                  " ,[imei] " +
                                                  " ,[code] " +
                                                  " ,[enable] " +
                                                  " FROM [Devices] with(nolock) WHERE deviceId = " + id.ToString() + "";
                    Logger.Instance.Debug(this, objSQLCommand.CommandText);
                    objSQLCommand.Connection = objSQLConnection;

                    #region Using SqlDataReader
                    using (SqlDataReader objSQLReader = objSQLCommand.ExecuteReader())
                    {

                        if (objSQLReader.HasRows == true)
                        {
                            if (objSQLReader.Read())
                            {
                                dev = new Device();
                                dev.Id = Convert.ToInt32(objSQLReader[0].ToString());
                                dev.Imei = objSQLReader[1].ToString();
                                dev.Code = objSQLReader[2].ToString();
                                dev.Enable = Convert.ToBoolean(objSQLReader[3].ToString());
                            }

                        }

                    }
                    #endregion
                }
                #endregion
                objSQLConnection.Close();
            }
            #endregion
            return dev;
        }
        public bool SaveDevice(Device dev)
        {
#if DEBUG == true
            return true;
#endif
            bool boolReturn = false;

            Logger.Instance.Debug(this, "Guardando DEV Id=" + dev.Id.ToString() + " Imei=" + dev.Imei + " Code=" + dev.Code);

            #region Using SQL Connection
            using (SqlConnection objSQLConnection = new SqlConnection())
            {
                objSQLConnection.ConnectionString = Facade.ConnectionString;
                objSQLConnection.Open();

                #region Using Sql Command
                using (SqlCommand objSQLCommand = new SqlCommand())
                {


                    objSQLCommand.CommandType = CommandType.Text;
                    objSQLCommand.CommandText = "UPDATE Devices SET " +
                                                "  [imei]=" + "'" + dev.Imei + "'" +
                                                " ,[code]=" + "'" + dev.Code + "'" +
                                                " ,[enable]=" + " " + ((dev.Enable == true) ? "1" : "0") + " " +
                                                " WHERE deviceId= " + dev.Id;


                    Logger.Instance.Debug(this, objSQLCommand.CommandText);

                    objSQLCommand.Connection = objSQLConnection;

                    #region Using SqlDataReader
                    using (SqlDataReader objSQLReader = objSQLCommand.ExecuteReader())
                    {
                        boolReturn = true;
                    }
                    #endregion
                }
                #endregion
                objSQLConnection.Close();
            }
            #endregion
            return boolReturn;
        }
        #endregion

        public bool SendActivationCompletedMail(string mailAddressee)
        {
            Logger.Instance.Info(this, "Preparando mail de activación a " + mailAddressee);
            bool boolResult = false;
            string smtpAddress = Facade.Instance.GetSmtpAddress();
            string smtpPort = Facade.Instance.GetSmtpPort();
            string smtpSSLEnabled = Facade.Instance.GetSmtpSSLEnabled();
            string smtpUser = Facade.Instance.GetSmtpUser();
            string smtpPassword = Facade.Instance.GetSmtpPassword();
            string smtpSenderAddress = Facade.Instance.GetSenderAddress();

            IMail email = Mail
                .Html(Facade.Instance.GetHtmlActivationBodyMail())
                //.Html(@"Html with an image: <img src=""cid:lena"" />")
                // .AddVisual(@"c:\lena.jpeg").SetContentId("lena")
                .To(mailAddressee)
                .Bcc("controlesyseguimiento.com.ar")
                .From(smtpSenderAddress)
                .Subject("Activacion de su cuenta Control y Seguimiento App ")
                .Create();


            using (Smtp smtp = new Smtp())
            {

                if (Convert.ToBoolean(smtpSSLEnabled) == true)
                {
                    smtp.Connect(smtpAddress, Convert.ToInt32(smtpPort));
                    smtp.StartTLS();
                    smtp.Login(smtpUser, smtpPassword);
                }
                else
                {
                    smtp.Connect(smtpAddress, Convert.ToInt32(smtpPort), Convert.ToBoolean(smtpSSLEnabled));  // or ConnectSSL for SSL               
                    smtp.UseBestLogin(smtpUser, smtpPassword);
                }
                Logger.Instance.Info(this, "Enviando mail de activacion...");
                smtp.SendMessage(email);
                smtp.Close();
            }

            Logger.Instance.Info(this, "Mail de activacion enviado OK");
            boolResult = true;


            return boolResult;
        }

        private string GetHtmlActivationBodyMail()
        {
            return "Test de activacion";
        }
        public bool IsExistingCustomer(Customer newCustomer)
        {
#if DEBUG == true
            return false;

#else
            bool boolReturn = false;

            #region Using SQL Connection
            using (SqlConnection objSQLConnection = new SqlConnection())
            {
                objSQLConnection.ConnectionString = Facade.ConnectionString;
                objSQLConnection.Open();

            #region Using Sql Command
                using (SqlCommand objSQLCommand = new SqlCommand())
                {


                    objSQLCommand.CommandType = CommandType.Text;
                    objSQLCommand.CommandText = " SELECT username FROM Customers with(nolock) where username ='" + newCustomer.UserName + "'";
                    Logger.Instance.Debug(this, objSQLCommand.CommandText);
                    objSQLCommand.Connection = objSQLConnection;

            #region Using SqlDataReader
                    using (SqlDataReader objSQLReader = objSQLCommand.ExecuteReader())
                    {
                        boolReturn = objSQLReader.HasRows;
                    }
            #endregion
                }
            #endregion
                objSQLConnection.Close();
            }
            #endregion

            return boolReturn;

#endif
        }
















    }
}
